package com.vanward.mes.scan.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class VerifyLotNumber {
    
    private static final String DB_URL = "jdbc:mysql://localhost:3306/mes?useSSL=false&serverTimezone=UTC";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "password";
    
    public static void main(String[] args) {
        String lotNumber = "2511000030102104010000025";
        
        System.out.println("Starting verification for lot number: " + lotNumber);
        
        try {
            // Use the application's data source or direct JDBC connection
            // This is a simplified test approach
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("Driver loaded");
            
            try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
                System.out.println("Database connected");
                
                // Query lot information
                String sql = "SELECT * FROM LOT WHERE Lot_No = ?";
                try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                    stmt.setString(1, lotNumber);
                    try (ResultSet rs = stmt.executeQuery()) {
                        if (rs.next()) {
                            System.out.println("\n--- VERIFICATION RESULT ---");
                            System.out.println("Lot exists in the system!");
                            System.out.println("\nLot Details:");
                            System.out.println("Lot Number: " + rs.getString("Lot_No"));
                            System.out.println("Lot ID: " + rs.getInt("Lot_Id"));
                            System.out.println("Status: " + rs.getString("Status"));
                            System.out.println("Start Time: " + rs.getString("Start_Time"));
                            System.out.println("End Time: " + rs.getString("End_Time"));
                            System.out.println("Work Order ID: " + rs.getInt("Work_Order_Id"));
                            System.out.println("Part ID: " + rs.getInt("Part_Id"));
                            
                            // Check if there's a finished log
                            int lotId = rs.getInt("Lot_Id");
                            checkFinishedLog(conn, lotId);
                            
                        } else {
                            System.out.println("\n--- VERIFICATION RESULT ---");
                            System.out.println("ERROR: Lot number " + lotNumber + " does not exist in the system!");
                        }
                    }
                }
            }
        } catch (Exception e) {
            System.out.println("\n--- VERIFICATION ERROR ---");
            System.out.println("Error during verification: " + e.getMessage());
            e.printStackTrace();
        }
    }
    
    private static void checkFinishedLog(Connection conn, int lotId) throws SQLException {
        String sql = "SELECT * FROM Lot_Finished_Log WHERE Lot_Id = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, lotId);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    System.out.println("\nFinished Log Exists:");
                    System.out.println("Log ID: " + rs.getInt("Id"));
                    System.out.println("Finished Time: " + rs.getString("Finished_Time"));
                    System.out.println("Is Auto: " + rs.getInt("Is_Auto"));
                } else {
                    System.out.println("\nNo finished log record found for this lot");
                }
            }
        }
    }
}